Natural Join
This lesson explains the natural join.
We'll cover the following
Natural Join#
In this lesson we’ll look at a syntactic sugar called NATURAL JOIN. The clause attempts to find the natural join between participating tables by matching on columns with same name.
Syntax for Natural Join#
SELECT *
FROM table1
NATURAL JOIN table2
Connect to the terminal below by clicking in the widget. Once connected, the command line prompt will show up. Enter or copy and paste the command ./DataJek/Lessons/29lesson.sh and wait for the MySQL prompt to start-up.
-
The NATURAL JOIN performs an inner join of the participating tables essentially without the user having to specify the matching columns. An example is as follows:
SELECT FirstName, SecondName, AssetType, URL
FROM Actors
NATURAL JOIN DigitalAssets;
Note that since none of the columns in the two tables share the same name, the result is a cartesian product. The screenshot shows the cartesian product only partially. The same result can be achieved using the inner join as follows:
SELECT FirstName, SecondName, AssetType, URL
FROM Actors
INNER JOIN DigitalAssets;
-
We’ll execute the above query again, but we’ll alter the column name for the DigitalAssets table from ActorID to ID so that it matches the column name in the Actors table.
-- Alter the column name
ALTER TABLE DigitalAssets CHANGE ActorId Id INT;
-- rerun the previous query
SELECT FirstName, SecondName, AssetType, URL
FROM Actors
NATURAL JOIN DigitalAssets;
You can observe from the results that the server matched the columns with the same name in both the tables and we get results equivalent to the following inner join query:
SELECT FirstName, SecondName, AssetType, URL
FROM Actors
INNER JOIN DigitalAssets USING (Id);
Under the hood, a natural join query is translated into an inner join query with matching column names ending up inside the using clause.
-
We can also ask for natural left and right joins. As an example, we show a natural left join below:
SELECT FirstName, SecondName, AssetType, URL
FROM Actors
NATURAL LEFT OUTER JOIN DigitalAssets;
From the output you can see there’s nothing magical about the natural join, it’s just syntactic sugar that implicitly finds the columns to join the tables. Ideally, we should write expressive queries and avoid using the natural join as it hides the columns that’ll be used for the join and can subtly introduce bugs. Imagine a situation where a table is altered to have an additional column that has the same name as a column in another table which is naturally joined with the first table in an existing query. Suddenly, the results from the natural join query will stop to make sense.
